﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.SqlClient;

namespace Empires.RAL.FileDatabase
{
    public class SQLiteProvider
    {
        private string ConnectionString { get; set; }
        private bool connectionIsSet = false;
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="filePath">sqlite文件地址(包含sqlite文件名，e.g:d:\\db\\test.db3)</param>
        /// <param name="pwd">连接密码，如果未设密码则为String.Empty</param>
        public void SetConnectString(string filePath, string pwd)
        {
            SqlConnectionStringBuilder sqlitesb = new SqlConnectionStringBuilder();
            sqlitesb.Pooling = true;
            if (!String.IsNullOrEmpty(pwd))
            {
                sqlitesb.Password = pwd;
            }
            if (System.IO.File.Exists(filePath))
            {
                sqlitesb.DataSource = filePath;
                ConnectionString = sqlitesb.ToString();
                connectionIsSet = true;
            }
            else
            {
                throw new ArgumentException(String.Format("对于指定路径为：{0}的sqlite文件不存在", filePath));
            }
        }
        public void SetConnectString(string connectionstring)
        {
            if (connectionstring.IndexOf("|DataDirectory|") > 0)
            {
                connectionstring = connectionstring.Replace("|DataDirectory|", AppDomain.CurrentDomain.GetData("DataDirectory").ToString());
            }
            ConnectionString = connectionstring;
            connectionIsSet = true;
        }
        public void CreateNewDataBase(string filePath)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(filePath);
            if (!fileInfo.Exists)
            {
                if (!fileInfo.Directory.Exists)
                {
                    System.IO.Directory.CreateDirectory(fileInfo.Directory.FullName);
                }
                SQLiteConnection.CreateFile(filePath);
            }
        }
        private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params KeyValuePair<string,string>[] p)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            if (p != null)
            {
                foreach (var parm in p)
                    cmd.Parameters.AddWithValue(parm.Key, parm.Value);
            }
        }
        public DataSet ExecuteQuery(string cmdText, params KeyValuePair<string, string>[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    DataSet ds = new DataSet();
                    PrepareCommand(command, conn, cmdText, p);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    da.Fill(ds);
                    return ds;
                }
            }
        }
        public int ExecuteNonQuery(string cmdText, params KeyValuePair<string, string>[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteNonQuery();
                }
            }
        }
        public SQLiteDataReader ExecuteReader(string cmdText, params KeyValuePair<string, string>[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }
        public object ExecuteScalar(string cmdText, params KeyValuePair<string, string>[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteScalar();
                }
            }
        }
    }
}
